package com.tangdi.mkfile.gzdwtzs;

import java.io.File;
import java.io.FileOutputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import com.tangdi.ReportFileInfo;
import com.tangdi.Utils;
import com.tangdi.Bean.CZGZ_GZLB;
import com.tangdi.Bean.CZGZ_SJGZ;
import com.tangdi.Bean.CZGZ_ZGGZMX;
import com.tangdi.Bean.DW_DM_MC;
import com.tangdi.Bean.XM_BM_XM_MC;
import com.tangdi.Dao.impl.GongziMXImpl;

/**
 * @author yang
 *
 */
public class CreateDWGZExcel_TZS {
	private Logger logger = Logger.getLogger(CreateDWGZExcel_TZS.class);
	private String excel_url_root = Utils.getFilePath("root_path");
	private String folder_Name = ReportFileInfo.FOLDER_NAME_GZDWTZS.getName();
	private String file_Type = "excel";
	private String file_name_type = ReportFileInfo.FILE_NAME_GZDWTZS.getName();
	
	private String title_content = ReportFileInfo.FILE_NAME_GZDWTZS_TITLE.getName();
	private String transfer_content_01 = ReportFileInfo.FILE_NAME_GZDWTZS_CONTENT01.getName();
	private String transfer_content_02 = ReportFileInfo.FILE_NAME_GZDWTZS_CONTENT02.getName();
	private String transfer_content_03 = ReportFileInfo.FILE_NAME_GZDWTZS_CONTENT03.getName();
	private String transfer_content_030 = ReportFileInfo.FILE_NAME_GZDWTZS_CONTENT030.getName();
	private String transfer_content_04 = ReportFileInfo.FILE_NAME_GZDWTZS_CONTENT04.getName();
	
	private DecimalFormat decimalFormat=new DecimalFormat("#0.00");	
	
	public void createExcel(Map<String,List<XM_BM_XM_MC>> map_bm_mc_list,List<DW_DM_MC> listDW_DM_MC,List<CZGZ_GZLB> list_gz_lb,String month){
		StringBuffer sql_select001 = null;
		StringBuffer sql_select002 = null;
		String sql_select_count001 = null;
		String sql_select_count002 = null;
		int gz_colNumMax = 0;
		String GZ_LB_DM001 = "5";
		String work_status_cde01 = "01";//在职
		String work_status_cde02 = "04";//病休
		String GZ_LB_DM002 = "13";
		String work_status_cde03 = "05";//离休
		String work_status_cde04 = "06";//退休
		
		String excel_url = "";
		GongziMXImpl gzmx = new GongziMXImpl();
		try {
			//listDW_DM_MC.size()
			for(int i = 0; i < listDW_DM_MC.size(); i++){
				logger.info(listDW_DM_MC.get(i).getDM() + "--" + listDW_DM_MC.get(i).getMC());
				HSSFWorkbook wb = new HSSFWorkbook();        //字体 
				Font headerFont = getFont(wb, "微软雅黑", 18);
				Font contextFont = getFont(wb, "微软雅黑", 12);
				//样式 
				CellStyle headerStyle = getCellStyle(wb, "noboder", CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
				headerStyle.setFont(headerFont);
				CellStyle contentStyle = getCellStyle(wb, "noboder", CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_CENTER);
				contentStyle.setFont(contextFont);
				CellStyle contentStyle2 = getCellStyle(wb, "noboder", CellStyle.ALIGN_RIGHT, CellStyle.VERTICAL_CENTER);
				contentStyle2.setFont(contextFont);
				CellStyle nameStyle = getCellStyle(wb, "border", CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_BOTTOM);
				nameStyle.setFont(contextFont);
				CellStyle valueStyle = getCellStyle(wb, "border", CellStyle.ALIGN_RIGHT, CellStyle.VERTICAL_CENTER);
				valueStyle.setFont(contextFont);
				CellStyle lastStyle_left = getCellStyle(wb, "noboder", CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_CENTER);
				lastStyle_left.setFont(contextFont);
				CellStyle lastStyle_center = getCellStyle(wb, "noboder", CellStyle.ALIGN_CENTER_SELECTION, CellStyle.VERTICAL_CENTER);
				lastStyle_center.setFont(contextFont);
				
				//String rs_Count001 = "0";
				String rs_WorkStatus01 = "0";
				String rs_WorkStatus02 = "0";
				//String rs_Count002 = "0";
				String rs_WorkStatus03 = "0";
				String rs_WorkStatus04 = "0";
				String km_Info001 = "";
				String km_MC001 = "";
				String km_DM001 = "";
				String km_Info002 = "";
				String km_MC002 = "";
				String km_DM002 = "";
				
				
				DW_DM_MC dw_dm_mc = listDW_DM_MC.get(i);//获取单位代码、名称
				excel_url = excel_url_root + "/" + month + "/"+ file_Type + "/" +folder_Name +"/" + month + "_"+ file_name_type + "_" + dw_dm_mc.getDM() +"_" + dw_dm_mc.getMC() + ".xls";
				File file = new File(excel_url);
				if(!file.exists()){
				    //先得到文件的上级目录，并创建上级目录，在创建文件
					file.getParentFile().mkdirs();
					file.createNewFile();
				}
				//单位  在职员工  工资汇总 start
				sql_select001 = new StringBuffer("SELECT ");
				List<XM_BM_XM_MC> list_BM_MC001 = map_bm_mc_list.get(GZ_LB_DM001);
				
				for(int m = 0; m < list_BM_MC001.size(); m++){
					sql_select001.append("coalesce(TO_CHAR(to_number(SUM("+ list_BM_MC001.get(m).getXM_BM() +")),'9999,999,999,990.00'),'0.00') AS " +list_BM_MC001.get(m).getXM_BM() + ",");
				}				
				sql_select_count001 = sql_select001.substring(0, sql_select001.length() - 1);
				//CZGZ_ZGGZMX gzDW_Count001 = new CZGZ_ZGGZMX();
				//gzDW_Count001 = gzmx.getCountGZByLB_DW(month, dw_dm_mc.getDM(), GZ_LB_DM001, list_BM_MC001, sql_select_count001);
				CZGZ_ZGGZMX gzDW_Count_WorkStatus_01 = new CZGZ_ZGGZMX();
				CZGZ_ZGGZMX gzDW_Count_WorkStatus_02 = new CZGZ_ZGGZMX();
				gzDW_Count_WorkStatus_01 = gzmx.getCountGZByLB_WorkCode(month, dw_dm_mc.getDM(), GZ_LB_DM001, list_BM_MC001, sql_select_count001,work_status_cde01);
				gzDW_Count_WorkStatus_02 = gzmx.getCountGZByLB_WorkCode(month, dw_dm_mc.getDM(), GZ_LB_DM001, list_BM_MC001, sql_select_count001,work_status_cde02);
				//rs_Count001 = gzmx.getDWRSCount_ByLB(month, "SELECT COUNT(1) FROM CZGZ_ZGGZMX WHERE YF = '" + month +"' AND DW_DM = '" + dw_dm_mc.getDM() + "' AND GZ_LB_DM = '"+ GZ_LB_DM001 +"'" );
				rs_WorkStatus01 = gzmx.getDWRSCount_ByLB(month, "SELECT COUNT(1) FROM CZGZ_ZGGZMX WHERE YF = '" + month +"' AND DW_DM = '" + dw_dm_mc.getDM() + "' AND GZ_LB_DM = '"+ GZ_LB_DM001 +"'" + " AND WORK_STATUS_CDE = '"+ work_status_cde01 +"'");
				rs_WorkStatus02 = gzmx.getDWRSCount_ByLB(month, "SELECT COUNT(1) FROM CZGZ_ZGGZMX WHERE YF = '" + month +"' AND DW_DM = '" + dw_dm_mc.getDM() + "' AND GZ_LB_DM = '"+ GZ_LB_DM001 +"'" + " AND WORK_STATUS_CDE = '"+ work_status_cde02 +"'");
				km_Info001 = gzmx.getDWRSCount_ByLB(month, "SELECT CONCAT(KM_MC,',',KM_DM) AS KM_MC_DM FROM CZGZ_ZGGZMX WHERE YF = '" + month +"' AND DW_DM = '" + dw_dm_mc.getDM() + "' AND GZ_LB_DM = '"+ GZ_LB_DM001 +"'  limit 1 " );
				if(km_Info001 != null && !km_Info001.equals("0")){
					if(km_Info001.indexOf(",") != 0){
						km_MC001 = km_Info001.substring(0, km_Info001.indexOf(","));
					}
					if(km_Info001.indexOf(",") != km_Info001.length()){
						km_DM001 = km_Info001.substring(km_Info001.indexOf(",") + 1, km_Info001.length());
					}
				}
				
				//单位  在职员工  工资汇总 end
				
				//单位  离职员工  工资汇总 start
				sql_select002 = new StringBuffer("SELECT ");
				List<XM_BM_XM_MC> list_BM_MC002 = map_bm_mc_list.get(GZ_LB_DM002);
				for(int n = 0; n < list_BM_MC002.size(); n++){
					sql_select002.append("coalesce(TO_CHAR(to_number(SUM("+ list_BM_MC002.get(n).getXM_BM() +")),2),'0.00') AS " +list_BM_MC002.get(n).getXM_BM() + ",");
				}
				sql_select_count002 = sql_select002.substring(0, sql_select002.length() - 1);
				//按工资类别、单位代码，汇总工资明细各科目的值
				//CZGZ_ZGGZMX gzDW_Count002 = new CZGZ_ZGGZMX();
				//gzDW_Count002 = gzmx.getCountGZByLB_DW(month, dw_dm_mc.getDM(), GZ_LB_DM002, list_BM_MC002, sql_select_count002);
				CZGZ_ZGGZMX gzDW_Count_WorkStatus_03 = new CZGZ_ZGGZMX();
				CZGZ_ZGGZMX gzDW_Count_WorkStatus_04 = new CZGZ_ZGGZMX();
				gzDW_Count_WorkStatus_03 = gzmx.getCountGZByLB_WorkCode(month, dw_dm_mc.getDM(), GZ_LB_DM002, list_BM_MC002, sql_select_count002,work_status_cde03);
				gzDW_Count_WorkStatus_04 = gzmx.getCountGZByLB_WorkCode(month, dw_dm_mc.getDM(), GZ_LB_DM002, list_BM_MC002, sql_select_count002,work_status_cde04);
				//rs_Count002 = gzmx.getDWRSCount_ByLB(month, "SELECT COUNT(1) FROM CZGZ_ZGGZMX WHERE YF = '" + month +"' AND DW_DM = '" + dw_dm_mc.getDM() + "' AND GZ_LB_DM = '"+ GZ_LB_DM002 +"'" );
				rs_WorkStatus03 = gzmx.getDWRSCount_ByLB(month, "SELECT COUNT(1) FROM CZGZ_ZGGZMX WHERE YF = '" + month +"' AND DW_DM = '" + dw_dm_mc.getDM() + "' AND GZ_LB_DM = '"+ GZ_LB_DM002 +"'" + " AND WORK_STATUS_CDE = '"+ work_status_cde03 +"'");
				rs_WorkStatus04 = gzmx.getDWRSCount_ByLB(month, "SELECT COUNT(1) FROM CZGZ_ZGGZMX WHERE YF = '" + month +"' AND DW_DM = '" + dw_dm_mc.getDM() + "' AND GZ_LB_DM = '"+ GZ_LB_DM002 +"'" + " AND WORK_STATUS_CDE = '"+ work_status_cde04 +"'");
				km_Info002 = gzmx.getDWRSCount_ByLB(month, "SELECT CONCAT(KM_MC,',',KM_DM) AS KM_MC_DM FROM CZGZ_ZGGZMX WHERE YF = '" + month +"' AND DW_DM = '" + dw_dm_mc.getDM() + "' AND GZ_LB_DM = '"+ GZ_LB_DM002 +"'  limit 1 " );
				if(km_Info002 != null && !km_Info002.equals("0")){
					if(km_Info002.indexOf(",") != 0){
						km_MC002 = km_Info002.substring(0, km_Info002.indexOf(","));	
					}
					if(km_Info002.indexOf(",") != km_Info002.length()){
						km_DM002 = km_Info002.substring(km_Info002.indexOf(",") + 1, km_Info002.length());
					}
				}
				
				//单位  离职员工  工资汇总 end				
				// 创建Excel的工作sheet,对应到一个excel文档的tab
				Sheet sheet = wb.createSheet("sheet1");
				sheet.setColumnWidth(0, 6*256);
				sheet.setColumnWidth(1, 10*256);
				sheet.setColumnWidth(2, 15*256);
				sheet.setColumnWidth(3, 15*256);
				sheet.setColumnWidth(4, 15*256);
				sheet.setColumnWidth(5, 3*256);
				sheet.setColumnWidth(6, 6*256);
				sheet.setColumnWidth(7, 10*256);
				sheet.setColumnWidth(8, 15*256);
				sheet.setColumnWidth(9, 15*256);
				sheet.setColumnWidth(10, 15*256);
				sheet.setColumnWidth(11, 15*256);
				
				Font font = getFont(wb, "", 18);
				CellStyle style = wb.createCellStyle();
				style.setFont(font);// 设置字体
				
				//第 1 行  标题
				Row row00 = sheet.createRow(0);
				addCell_Value(row00, 0, title_content, headerStyle);
				//第 2 行 单位称谓 编号
				Row row10 = sheet.createRow(1);
				addCell_Value(row10, 0, dw_dm_mc.getMC().toString() + "：", contentStyle);
				addCell_Value(row10, 8, "编号：JH" + month + dw_dm_mc.getDM(), contentStyle2);

				//第 3 行 通知内容
				Row row20 = sheet.createRow(2);
				addCell_Value(row20, 0, transfer_content_01 + month.substring(0, 4) + "年" + month.substring(4, 6)+ "月" + 
	                      transfer_content_02 + 
	                      getStringDoubleAdd(gzDW_Count_WorkStatus_01.getSF_TXN_AMT(),
				                    				   gzDW_Count_WorkStatus_02.getSF_TXN_AMT(),
				                    				   gzDW_Count_WorkStatus_01.getB009() == null ? "0.00" : gzDW_Count_WorkStatus_01.getB009(),
				                    				   gzDW_Count_WorkStatus_02.getB009() == null ? "0.00" : gzDW_Count_WorkStatus_02.getB009(),
													   gzDW_Count_WorkStatus_03.getSF_TXN_AMT(),
				                                       gzDW_Count_WorkStatus_04.getSF_TXN_AMT()
				                    				   ) +
	                      transfer_content_03 + 
	                      transfer_content_030 +
						  getStringDoubleAdd(gzDW_Count_WorkStatus_01.getB009(), gzDW_Count_WorkStatus_02.getB009()) +
	                      transfer_content_04, contentStyle);
				
				//第 5 行 工资类别
				Row row50 = sheet.createRow(5);
				addCell_Value(row50, 0, "工资类别：机关", contentStyle);
				addCell_Value(row50, 6, "工资类别：离退休", contentStyle);
				//第 6  行 项目名称
				Row row60 = sheet.createRow(6);
				addCell_Value(row60, 0, "项目", nameStyle);
				addCell_Value(row60, 1, "", nameStyle);
				addCell_Value(row60, 2, "人员类别", nameStyle);
				addCell_Value(row60, 3, "", nameStyle);
				addCell_Value(row60, 4, "", nameStyle);
				addCell_Value(row60, 6, "项目", nameStyle);
				addCell_Value(row60, 7, "", nameStyle);
				addCell_Value(row60, 8, "人员类别", nameStyle);
				addCell_Value(row60, 9, "", nameStyle);
				addCell_Value(row60, 10, "", nameStyle);
				//第 7 行  在职 合计 等
				Row row70 = sheet.createRow(7);
				addCell_Value(row70, 0, "", nameStyle);
				addCell_Value(row70, 1, "", nameStyle);
				addCell_Value(row70, 2, "在职", nameStyle);
				addCell_Value(row70, 3, "离岗退养", nameStyle);
				addCell_Value(row70, 4, "合计", nameStyle);
				
				addCell_Value(row70, 6, "", nameStyle);
				addCell_Value(row70, 7, "", nameStyle);
				addCell_Value(row70, 8, "在职", nameStyle);
				addCell_Value(row70, 9, "离岗退养", nameStyle);
				addCell_Value(row70, 10, "合计", nameStyle);
				//第 8 行 人数  
				Row row80 = sheet.createRow(8);
				addCell_Value(row80, 0, "人        数", nameStyle);
				addCell_Value(row80, 1, "", valueStyle);
				addCell_Value(row80, 2, rs_WorkStatus01, valueStyle);
				addCell_Value(row80, 3, rs_WorkStatus02, valueStyle);
				addCell_Value(row80, 4, getStringIntegerAdd(rs_WorkStatus01, rs_WorkStatus02), valueStyle); 
	
				addCell_Value(row80, 6, "人        数", nameStyle);
				addCell_Value(row80, 7, "", valueStyle);
				addCell_Value(row80, 8, rs_WorkStatus03, valueStyle);
				addCell_Value(row80, 9, rs_WorkStatus04, valueStyle);
				addCell_Value(row80, 10, getStringIntegerAdd(rs_WorkStatus03, rs_WorkStatus04), valueStyle);
				//第 9 行 科目
				Row row90 = sheet.createRow(9);
				addCell_Value(row90, 0, "科目", nameStyle);
				addCell_Value(row90, 1, "名称", nameStyle);
				addCell_Value(row90, 2, km_MC001, nameStyle);
				addCell_Value(row90, 3, "", valueStyle);
				addCell_Value(row90, 4, "", valueStyle);
				
				addCell_Value(row90, 6, "科目", nameStyle);
				addCell_Value(row90, 7, "名称", nameStyle);
				addCell_Value(row90, 8, km_MC002, nameStyle);
				addCell_Value(row90, 9, "", valueStyle);
				addCell_Value(row90, 10, "", valueStyle);
				
				//第 10 行 科目
				Row row100 = sheet.createRow(10);
				addCell_Value(row100, 0, "", nameStyle);
				addCell_Value(row100, 1, "代码", nameStyle);
				addCell_Value(row100, 2, km_DM001, nameStyle);
				addCell_Value(row100, 3, "", valueStyle);
				addCell_Value(row100, 4, "", valueStyle);
				
				addCell_Value(row100, 6, "", nameStyle);
				addCell_Value(row100, 7, "代码", nameStyle);
				addCell_Value(row100, 8, km_DM002, nameStyle);
				addCell_Value(row100, 9, "", nameStyle);
				addCell_Value(row100, 10, "", valueStyle);
				//第 11 行 工资来源  预算内 
				Row row101 = sheet.createRow(11);
				addCell_Value(row101, 0, "工资来源", nameStyle);
				addCell_Value(row101, 1, "预算内", nameStyle);
				addCell_Value(row101, 2, gzDW_Count_WorkStatus_01.getYF_GZ(), valueStyle);
				addCell_Value(row101, 3, gzDW_Count_WorkStatus_02.getYF_GZ(), valueStyle);
				addCell_Value(row101, 4, getStringDoubleAdd(gzDW_Count_WorkStatus_01.getYF_GZ(), gzDW_Count_WorkStatus_02.getYF_GZ()), valueStyle);
				
				addCell_Value(row101, 6, "工资来源", nameStyle);
				addCell_Value(row101, 7, "预算内", nameStyle);
				addCell_Value(row101, 8, gzDW_Count_WorkStatus_03.getYF_GZ(), valueStyle);
				addCell_Value(row101, 9, gzDW_Count_WorkStatus_04.getYF_GZ(), valueStyle);
				addCell_Value(row101, 10, getStringDoubleAdd(gzDW_Count_WorkStatus_03.getYF_GZ(), gzDW_Count_WorkStatus_04.getYF_GZ()), valueStyle);
				//第 12 行 工资来源   预算外 
				Row row102 = sheet.createRow(12);
				addCell_Value(row102, 0, "", nameStyle);
				addCell_Value(row102, 1, "预算外", nameStyle);
				addCell_Value(row102, 2, "0.00", valueStyle);
				addCell_Value(row102, 3, "0.00", valueStyle);
				addCell_Value(row102, 4, "0.00", valueStyle);
	
				addCell_Value(row102, 6, "", nameStyle);
				addCell_Value(row102, 7, "预算外", nameStyle);
				addCell_Value(row102, 8, "0.00", valueStyle);
				addCell_Value(row102, 9, "0.00", valueStyle);
				addCell_Value(row102, 10, "0.00", valueStyle);
				//第 13 行 工资 应发
				Row row103 = sheet.createRow(13);
				addCell_Value(row103, 0, "工资", nameStyle);
				addCell_Value(row103, 1, "应发", nameStyle);
				addCell_Value(row103, 2, gzDW_Count_WorkStatus_01.getYF_GZ(), valueStyle);
				addCell_Value(row103, 3, gzDW_Count_WorkStatus_02.getYF_GZ(), valueStyle);
				addCell_Value(row103, 4, getStringDoubleAdd(gzDW_Count_WorkStatus_01.getYF_GZ(), gzDW_Count_WorkStatus_02.getYF_GZ()), valueStyle);
	
				addCell_Value(row103, 6, "工资", nameStyle);
				addCell_Value(row103, 7, "应发", nameStyle);
				addCell_Value(row103, 8, gzDW_Count_WorkStatus_03.getYF_GZ(), valueStyle);
				addCell_Value(row103, 9, gzDW_Count_WorkStatus_04.getYF_GZ(), valueStyle);
				addCell_Value(row103, 10, getStringDoubleAdd(gzDW_Count_WorkStatus_03.getYF_GZ(), gzDW_Count_WorkStatus_04.getYF_GZ()), valueStyle);
				//第 14 行 工资 扣款小计
				Row row104 = sheet.createRow(14);
				addCell_Value(row104, 0, "", nameStyle);
				addCell_Value(row104, 1, "扣款小计", nameStyle);
				addCell_Value(row104, 2, gzDW_Count_WorkStatus_01.getDK_XJ(), valueStyle);
				addCell_Value(row104, 3, gzDW_Count_WorkStatus_02.getDK_XJ(), valueStyle);
				addCell_Value(row104, 4, getStringDoubleAdd(gzDW_Count_WorkStatus_01.getDK_XJ(), gzDW_Count_WorkStatus_02.getDK_XJ()), valueStyle);
				
				addCell_Value(row104, 6, "", nameStyle);
				addCell_Value(row104, 7, "扣款小计", nameStyle);
				addCell_Value(row104, 8, gzDW_Count_WorkStatus_03.getDK_XJ(), valueStyle);
				addCell_Value(row104, 9, gzDW_Count_WorkStatus_04.getDK_XJ(), valueStyle);
				addCell_Value(row104, 10, getStringDoubleAdd(gzDW_Count_WorkStatus_03.getDK_XJ(), gzDW_Count_WorkStatus_04.getDK_XJ()), valueStyle);
				
				//第 15 行 工资 实发
				Row row105 = sheet.createRow(15);
				addCell_Value(row105, 0, "", nameStyle);
				addCell_Value(row105, 1, "实发", nameStyle);
				addCell_Value(row105, 2, gzDW_Count_WorkStatus_01.getSF_TXN_AMT(), valueStyle);
				addCell_Value(row105, 3, gzDW_Count_WorkStatus_02.getSF_TXN_AMT(), valueStyle);
				addCell_Value(row105, 4, getStringDoubleAdd(gzDW_Count_WorkStatus_01.getSF_TXN_AMT(), gzDW_Count_WorkStatus_02.getSF_TXN_AMT()), valueStyle);
				
				addCell_Value(row105, 6, "", nameStyle);
				addCell_Value(row105, 7, "实发", nameStyle);
				addCell_Value(row105, 8, gzDW_Count_WorkStatus_03.getSF_TXN_AMT(), valueStyle);
				addCell_Value(row105, 9, gzDW_Count_WorkStatus_04.getSF_TXN_AMT(), valueStyle);
				addCell_Value(row105, 10, getStringDoubleAdd(gzDW_Count_WorkStatus_03.getSF_TXN_AMT(), gzDW_Count_WorkStatus_04.getSF_TXN_AMT()), valueStyle);
	
				gz_colNumMax = list_BM_MC001.size();
				if(gz_colNumMax < list_BM_MC002.size()){
					gz_colNumMax = list_BM_MC002.size();
				}
				for(int k = 0; k < gz_colNumMax - 2; k++){//根据工资类别反射查找工资科目
					Row rowTemp = sheet.createRow(16 + k);
					if(k >=  (list_BM_MC001.size() - 2)){
						addCell_Value(rowTemp, 0, "", nameStyle);
						addCell_Value(rowTemp, 1, "", valueStyle);
						addCell_Value(rowTemp, 2, "", valueStyle);
						addCell_Value(rowTemp, 3, "", valueStyle);
						addCell_Value(rowTemp, 4, "", valueStyle);
					}else{
						String gz_value_WorkStatus01 = "0.00";
						String gz_value_WorkStatus02 = "0.00";
						if(gzDW_Count_WorkStatus_01 != null){
							gz_value_WorkStatus01 = (String)Utils.getFieldReflect(gzDW_Count_WorkStatus_01,CZGZ_ZGGZMX.class,list_BM_MC001.get(k).getXM_BM());
						}
						if(gzDW_Count_WorkStatus_02 != null){
							gz_value_WorkStatus02 = (String)Utils.getFieldReflect(gzDW_Count_WorkStatus_02,CZGZ_ZGGZMX.class,list_BM_MC001.get(k).getXM_BM());
						}
						addCell_Value(rowTemp, 0, list_BM_MC001.get(k).getXM_MC(), nameStyle);
						addCell_Value(rowTemp, 1, "", valueStyle);
						addCell_Value(rowTemp, 2, gz_value_WorkStatus01, valueStyle);
						addCell_Value(rowTemp, 3, gz_value_WorkStatus02, valueStyle);
						addCell_Value(rowTemp, 4, getStringDoubleAdd(gz_value_WorkStatus01, gz_value_WorkStatus02), valueStyle);
					}
								
					if( k >= (list_BM_MC002.size() - 2)){
						addCell_Value(rowTemp, 6, "", nameStyle);
						addCell_Value(rowTemp, 7, "", nameStyle);
						addCell_Value(rowTemp, 8, "", valueStyle);
						addCell_Value(rowTemp, 9, "", valueStyle);
						addCell_Value(rowTemp, 10, "", valueStyle);
					}else{
						String gz_value_WorkStatus03 = "0.00";
						String gz_value_WorkStatus04 = "0.00";
						if(gzDW_Count_WorkStatus_03 != null){
							gz_value_WorkStatus03 = (String)Utils.getFieldReflect(gzDW_Count_WorkStatus_03,CZGZ_ZGGZMX.class,list_BM_MC002.get(k).getXM_BM());							
						}
						if(gzDW_Count_WorkStatus_04 != null){
							gz_value_WorkStatus04 = (String)Utils.getFieldReflect(gzDW_Count_WorkStatus_04,CZGZ_ZGGZMX.class,list_BM_MC002.get(k).getXM_BM());							
						}						addCell_Value(rowTemp, 6, list_BM_MC002.get(k).getXM_MC(), nameStyle);
						addCell_Value(rowTemp, 7, "", nameStyle);
						addCell_Value(rowTemp, 8, gz_value_WorkStatus03, valueStyle);
						addCell_Value(rowTemp, 9, gz_value_WorkStatus04, valueStyle);
						addCell_Value(rowTemp, 10, getStringDoubleAdd(gz_value_WorkStatus03, gz_value_WorkStatus04), valueStyle);
					}
					}			
		
				Row row_last = sheet.createRow(gz_colNumMax + 16);
				Cell row_last01 = row_last.createCell(0);
				row_last01.setCellValue("代理银行业务章：");
				row_last01.setCellStyle(lastStyle_left);
				
				Cell row_last02 = row_last.createCell(3);
				row_last02.setCellValue("负责人：");
				row_last02.setCellStyle(lastStyle_center);
		
				Cell row_last03 = row_last.createCell(5);
				row_last03.setCellValue("经办人：");
				row_last03.setCellStyle(lastStyle_center);
				
				Cell row_last04 = row_last.createCell(8);
				row_last04.setCellValue("制表日期：2019年05月");
				row_last04.setCellStyle(lastStyle_center);
				// 合并单元格
				sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));//标题
				sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7)); //单位名称
				sheet.addMergedRegion(new CellRangeAddress(1, 1, 8, 10));//单位编号
				sheet.addMergedRegion(new CellRangeAddress(2, 4, 0, 10));//通知内容
				sheet.addMergedRegion(new CellRangeAddress(5, 5, 0, 4));//工资类别
				sheet.addMergedRegion(new CellRangeAddress(5, 5, 6, 10));//工资类别
				sheet.addMergedRegion(new CellRangeAddress(6, 7, 0, 1));//项目
				sheet.addMergedRegion(new CellRangeAddress(6, 6, 2, 4));//人员类别
				sheet.addMergedRegion(new CellRangeAddress(6, 7, 6, 7));//项目
				sheet.addMergedRegion(new CellRangeAddress(6, 6, 8, 10));//人员类别
				sheet.addMergedRegion(new CellRangeAddress(8, 8, 0, 1));//人数
				sheet.addMergedRegion(new CellRangeAddress(8, 8, 6, 7));//人数
				sheet.addMergedRegion(new CellRangeAddress(9, 10, 0, 0));//科目
				sheet.addMergedRegion(new CellRangeAddress(9, 10, 6, 6));//科目
				sheet.addMergedRegion(new CellRangeAddress(11, 12, 0, 0));//工资来源
				sheet.addMergedRegion(new CellRangeAddress(11, 12, 6, 6));//工资来源
				sheet.addMergedRegion(new CellRangeAddress(13, 15, 0, 0));//工资
				sheet.addMergedRegion(new CellRangeAddress(13, 15, 6, 6));//工资
				for(int a = 0; a < gz_colNumMax - 2; a++){
					sheet.addMergedRegion(new CellRangeAddress(a + 16, a + 16, 0, 1));
					sheet.addMergedRegion(new CellRangeAddress(a + 16, a + 16, 6, 7));
				}
				sheet.addMergedRegion(new CellRangeAddress(gz_colNumMax + 16, gz_colNumMax + 16, 0, 2));
				sheet.addMergedRegion(new CellRangeAddress(gz_colNumMax + 16, gz_colNumMax + 16, 3, 4));
				sheet.addMergedRegion(new CellRangeAddress(gz_colNumMax + 16, gz_colNumMax + 16, 5, 7));
				sheet.addMergedRegion(new CellRangeAddress(gz_colNumMax + 16, gz_colNumMax + 16, 8, 10));
				
				FileOutputStream os = new FileOutputStream(excel_url);
				wb.write(os);
				os.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public static CellStyle getCellStyle(HSSFWorkbook wb, String borderType, short x_align, short y_align){
		CellStyle style = wb.createCellStyle();
		style.setAlignment(x_align);//水平方向
		style.setVerticalAlignment(y_align);// 垂直方向
		if(borderType.equals("border")){
			style.setBorderTop(CellStyle.BORDER_THIN);
			style.setBorderBottom(CellStyle.BORDER_THIN);
			style.setBorderLeft(CellStyle.BORDER_THIN);
			style.setBorderRight(CellStyle.BORDER_THIN);
		}
		style.setLocked(true);
		style.setWrapText(true);
		
		return style;
	}
	public static Font getFont(HSSFWorkbook wb, String fontName, int fontSize){
		Font font = wb.createFont();
		font.setFontName(fontName);
		font.setFontHeightInPoints((short)fontSize);
		font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
		return font;
	}
	public static void addCell_Value(Row row, int a, String value, CellStyle cellStyle){
			Cell cell = row.createCell(a);
			cell.setCellValue(value);
			cell.setCellStyle(cellStyle);
	}
	public String getStringDoubleAdd(String ... values){
		double result = 0;
		for(String value : values){
			result = result + Double.parseDouble(value.trim());
		}
		return decimalFormat.format(result);
	}
	public String getStringIntegerAdd(String value01, String value02){
		return String.valueOf(Integer.parseInt(value01.trim()) +
							  Integer.parseInt(value02.trim()));
	}	
	
	public static void main(String[] args) {
		//年月
		String month = "201905";
		//根据单位编号
		List<DW_DM_MC> listDW_DM_MC=new ArrayList<DW_DM_MC>();
		GongziMXImpl gz_DW_List = new GongziMXImpl();
		listDW_DM_MC = gz_DW_List.getListDW_DM_MC(month);
		
		//工资类别，科目项信息
		Map<String,List<XM_BM_XM_MC>> map_bm_mc_list = new HashMap<String,List<XM_BM_XM_MC>>();
		
		//工资类别list
		List<CZGZ_GZLB> listlb=new ArrayList<CZGZ_GZLB>();
		GongziMXImpl gzlb = new GongziMXImpl();
		listlb = gzlb.listGZLBReport(month);
		
		//实际工资科目 按工资类别
		List<CZGZ_SJGZ> list_sjgz=new ArrayList<CZGZ_SJGZ>();
		GongziMXImpl sjgz = new GongziMXImpl();
		list_sjgz = sjgz.listSJGZReport(month);
		
		//组装工资类别 ，实际工资科目项，对应好关系；方便后续使用 list_BM_MC001
		for(int i=0;i<listlb.size();i++){
			List<XM_BM_XM_MC> bm_mc_list = new ArrayList<XM_BM_XM_MC>();
			System.out.println(listlb.get(i).getLB_BM().trim());
			for(int j=0; j<list_sjgz.size(); j++){
				XM_BM_XM_MC bm_mc= new XM_BM_XM_MC();
				if((listlb.get(i).getLB_BM().trim()).equals(list_sjgz.get(j).getGZ_LB().trim())){					
					bm_mc.setXM_BM(Utils.getXM_BM(list_sjgz.get(j).getXM_BM().trim()));
					bm_mc.setXM_MC(list_sjgz.get(j).getXM_MC().trim());
					System.out.print(Utils.getXM_BM(list_sjgz.get(j).getXM_BM().trim()) + " | ");
					bm_mc_list.add(bm_mc);
				}
			}
			System.out.println();
			map_bm_mc_list.put(listlb.get(i).getLB_BM().trim(), bm_mc_list);
		}
		
		CreateDWGZExcel_TZS c = new CreateDWGZExcel_TZS();
	    c.createExcel(map_bm_mc_list,listDW_DM_MC,listlb,month);
	    System.out.println("制作完成");

	}

}
